﻿using DotNetDAL.Extensions;
using DotNetDAL.Entity.DbStruct;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace DotNetDAL.DbStruct
{
    /// <summary>
    /// SqlServer 数据库表结构助手
    /// </summary>
    public class SqlServerDbStructHelper : IDbStructHelper, IDisposable
    {
        private SqlConnection _Connector { get; set; }
        private bool IsNewConnector { get; set; } = false;
        public SqlServerDbStructHelper(SqlConnection conn) {
            _Connector = conn;
            if(_Connector.State != ConnectionState.Open)
            {
                _Connector.Open();
            }
        }
        public SqlServerDbStructHelper(string ConnectionString)
        {
            _Connector = new SqlConnection(ConnectionString);
            _Connector.Open();
            IsNewConnector = true;
        }

        private SqlCommand CreateSqlCommand() {
            SqlCommand _SqlCommand = new SqlCommand();
            _SqlCommand.Connection = _Connector;
            _SqlCommand.CommandType = CommandType.Text;
            //以秒为单位
            _SqlCommand.CommandTimeout = 60;
            _SqlCommand.Parameters.Clear();
            return _SqlCommand;
        }

        #region IDisposable Support

        private bool disposedValue = false; // 要检测冗余调用
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (!disposedValue)
            {
                try {
                    if (IsNewConnector)
                    {
                        _Connector.Dispose();
                    }
                } catch { }
                disposedValue = true;
            }
        }
        #endregion

        /// <summary>
        /// 获取表字段列表
        /// </summary>
        /// <param name="table_name"></param>
        /// <returns></returns>
        public List<DbStructColumn> GetDbStructColumnList(string table_name)
        {
            string sql = @" 
select obj.name table_name,
col.name column_name
,t.name column_data_type
,col.length column_data_length
,case when (t.name='nvarchar' or t.name='varbinary' or t.name='varchar') and col.length=-1 then t.name+'(MAX)'
 when t.name='binary' or t.name='char' or t.name='nchar' 
    or t.name='nvarchar' or t.name='varbinary'or t.name='varchar'
  then t.name+'('+convert(varchar(50),col.prec)+')'
 when t.name='datetime2' or t.name='datetimeoffset'or t.name='time'
  then t.name+'('+convert(varchar(50),col.scale)+')'
 when t.name='decimal' or t.name='numeric'
  then t.name+'('+convert(varchar(50),col.prec)+','++convert(varchar(50),col.scale)+')'
 else t.name end column_data_type_text
,isnull(col.isnullable,0) isnull_able
,case when pkIndex.is_primary_key=1  then 1 else 0 end is_primary_key
,men.text default_value
,ep.[value] column_description
from syscolumns col
inner join sysobjects obj on col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0  
left join systypes t on col.xusertype = t.xusertype 
left join sys.extended_properties ep on  col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description' 
left join sys.index_columns colIndex on colIndex.object_id=col.id and colIndex.column_id=col.colid
left join sys.indexes pkIndex on pkIndex.object_id = colIndex.object_id and pkIndex.index_id = colIndex.index_id
left join syscomments men on men.id = col.cdefault
where obj.name=@table_name
";
            
            using(SqlCommand cmd = CreateSqlCommand())
            {
                cmd.CommandText = sql;
                cmd.Parameters.Add(new SqlParameter("@table_name"
                    , string.IsNullOrEmpty(table_name) ? (object)DBNull.Value : table_name));
                using (DataTable dt = new DataTable())
                {
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                    sda.Dispose();
                    return dt.ToList<DbStructColumn>();
                }
            }
        }

        /// <summary>
        /// 获取表
        /// </summary>
        /// <returns></returns>
        private DataTable GetTable()
        {
            string sql = @" select name table_name from sysobjects where xtype = 'U' AND status >= 0 ";
            using (SqlCommand cmd = CreateSqlCommand())
            {
                using (DataTable dt = new DataTable())
                {
                    cmd.CommandText = sql;
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                    sda.Dispose();

                    return dt;
                }
            }

        }
        /// <summary>
        /// 获取表列表
        /// </summary>
        /// <returns></returns>
        public List<DbStructTable> GetDbStructTableList()
        {
            List<DbStructTable> list = new List<DbStructTable>();
            using (DataTable dt = GetTable())
            {
                foreach(DataRow row in dt.Rows)
                {
                    DbStructTable table = new DbStructTable();
                    table.table_name = Convert.ToString(row["table_name"]);
                    table.columns = GetDbStructColumnList(table.table_name);
                    list.Add(table);
                }
            }
            return list;
        }
        /// <summary>
        /// 获取表列表
        /// </summary>
        /// <returns></returns>
        public List<string> GetTableList()
        {
            using (DataTable dt = GetTable())
            {
                return dt.ToStringList("table_name");
            }
        }
    }
}
